/**
 * Created by Administrator on 2017/6/3.
 */
var DBFactory = require('../db/mysql_db');
var async = require('async');
var userAppDao = module.exports;

userAppDao.getAppAgentList = function (data, cb) {
    if (!data) {
        cb(new Error(500));
    } else {

        DBFactory.getConnection(function (error, connection) {//使用transaction创建新用户
            if (error) {
                cb(error);
            } else {
                var appAgentInfo = [];
                async.waterfall([
                    // 开始Transaction
                    function (callback) {
                        connection.beginTransaction(function (err) {
                            callback(err);
                        });
                    },

                    //验证是否有最高的agent
                    function (callback) {
                        connection.query("select e.*,GROUP_CONCAT(f.group_name) group_name from (select a.*,c.agent_name,c.agent_score,c.name from user_app_new_agent a, " +
                            "(select d.name as agent_name,d.score as agent_score,d.app_sid,MAX(d.lvl) topLvl,b.name from app_agent d LEFT JOIN apps b ON d.app_sid = b.sid GROUP BY d.app_sid) c " +
                            "where a.app_sid = c.app_sid and a.agent_lvl = c.topLvl and a.uid = ?) e LEFT JOIN user_subaccount f ON e.uid = f.uid and e.app_sid = f.app_sid GROUP BY e.app_sid"
                            , [data]
                            , function (err, result) {
                                appAgentInfo = result;
                                // console.log(result);
                                callback(err, result);
                            });
                    },

                    //放入不是最高的agent
                    //我这边其实将group by 改成a.app_sid就不用分两次判断了
                    function (topAgentInfo, callback) {
                        connection.query("select a.*,b.score agent_score,b.name agent_name,c.name,GROUP_CONCAT(d.group_name) group_name " +
                            "from user_app_new_agent a left join app_agent b on a.app_sid = b.app_sid and b.lvl > a.agent_lvl " +
                            "left join apps c on a.app_sid = c.sid " +
                            "LEFT JOIN user_subaccount d on a.uid = d.uid and a.app_sid = d.app_sid " +
                            "where a.uid = ? " +
                            "GROUP BY b.app_sid"
                            , [data]
                            , function (err, result) {
                                if (err) {
                                    cb(err);
                                    return;
                                }
                                result.forEach(function (o) {
                                    appAgentInfo.push(o);
                                });
                                callback(null, true, appAgentInfo);
                            });
                    },
                ], function (DbErr, isSuccess, uidOrInfo) {
                    if (DbErr) {
                        connection.rollback(function () {
                            connection.release();
                            cb(DbErr);
                        });
                    } else if (!isSuccess) {
                        connection.rollback(function () {
                            connection.release();
                            cb(DbErr);
                        });
                    } else {
                        connection.commit(function (e) {
                            if (e) {
                                connection.rollback(function () {
                                    connection.release();
                                    cb(e);
                                });
                            } else {
                                connection.release();
                                cb(null, uidOrInfo);
                            }
                        });
                    }
                });
            }
        });

    }
}

userAppDao.getAppVipList = function (data, cb) {
    if (!data) {
        cb(new Error(500));
    } else {
        DBFactory.poolQuery('select * from user_vip where uid = ? and app_sid = ?', [data.uid, data.app_sid],
            function () {
                cb("no connection");
            }, function (err, result) {
                if (err) {
                    cb(err);
                    return;
                }
                if (result) {
                    cb(null, result);
                } else {
                    cb('该用户无vip列表');
                }
            });
    }
}

userAppDao.getAppItem = function (data, cb) {
    if (!data) {
        cb(new Error(500));
    } else {
        DBFactory.poolQuery('select * from apps where sid = ?', [data],
            function () {
                cb("no connection");
            }, function (err, result) {
                if (err) {
                    cb(err);
                    return;
                }
                if (result[0]) {
                    cb(null, result[0]);
                } else {
                    cb('无该应用');
                }
            });
    }
}

//获得下级代理
userAppDao.getLowAgentList = function (data, cb) {
    if (!data) {
        cb(new Error(500));
    } else {
        DBFactory.poolQuery('select a.*,b.name from user_app_new_agent a LEFT JOIN users b on a.uid = b.uid where a.recommender = ? and a.app_sid = ?', [data.account, data.app_sid],
            function () {
                cb("no connection");
            }, function (err, result) {
                if (err) {
                    cb(err);
                    return;
                }
                result.forEach(function (o) {
                    o.created_time = simpleDateFormat(o.created_time);
                    o.name = o.name ? o.name : "";
                })
                cb(null, result);
            });
    }
}

//获得下下级代理
userAppDao.getLowAgentList2 = function (data, cb) {
    if (!data) {
        cb(new Error(500));
    } else {
        DBFactory.poolQuery('select a.*,b.name from user_app_new_agent a LEFT JOIN users b on a.uid = b.uid where a.recommender2 = ? and a.app_sid = ?', [data.account, data.app_sid],
            function () {
                cb("no connection");
            }, function (err, result) {
                if (err) {
                    cb(err);
                    return;
                }
                result.forEach(function (o) {
                    o.created_time = simpleDateFormat(o.created_time);
                    o.name = o.name ? o.name : "";
                })
                cb(null, result);
            });
    }
}

//获得下级的订单
userAppDao.getAgentPriceList = function (data, cb) {
    if (!data) {
        cb(new Error(500));
        return;
    }
    DBFactory.getConnection(function (error, connection) {
        if (error) {
            cb(error);
            return;
        }
        connection.query('select uid from user_app_new_agent where recommender = ? and app_sid = ? ',
            [data.account, data.app_sid],
            function (err, result) {
                if (err) {
                    cb(err);
                    connection.release();
                    return;
                }
                var agentInfo = (result && result[0]) ? result[0] : undefined;
                if (!agentInfo) {
                    cb(null, null);
                    connection.release();
                    return;
                }

                var uids = [];
                result.forEach(function (o) {
                    uids.push(o.uid);
                });
                connection.query('select * from order_list where uid in (?) and app_sid = ? and status = 13',
                    [uids, data.app_sid],
                    function (err, result) {
                        if (err) {
                            cb(err);
                            connection.release();
                            return;
                        }
                        cb(null, result);
                        connection.release();
                    });

            });
    });
}

//获得下下级的订单
userAppDao.getAgentPriceList2 = function (data, cb) {
    if (!data) {
        cb(new Error(500));
        return;
    }
    DBFactory.getConnection(function (error, connection) {
        if (error) {
            cb(error);
            return;
        }
        connection.query('select uid from user_app_new_agent where recommender2 = ? and app_sid = ?',
            [data.account, data.app_sid],
            function (err, result) {
                if (err) {
                    cb(err);
                    connection.release();
                    return;
                }
                var agentInfo = (result && result[0]) ? result[0] : undefined;
                if (!agentInfo) {
                    cb(null, null);
                    connection.release();
                    return;
                }

                var uids = [];
                result.forEach(function (o) {
                    uids.push(o.uid);
                });
                console.log(uids);
                connection.query('select * from order_list where uid in (?) and app_sid = ? and status = 13',
                    [uids, data.app_sid],
                    function (err, result) {
                        if (err) {
                            cb(err);
                            connection.release();
                            return;
                        }
                        cb(null, result);
                        connection.release();
                    });

            });
    });
}


//获得下下级的订单
userAppDao.getAgentPriceList2 = function (data, cb) {
    if (!data) {
        cb(new Error(500));
        return;
    }
    DBFactory.getConnection(function (error, connection) {
        if (error) {
            cb(error);
            return;
        }
        connection.query('select uid from user_app_new_agent where recommender2 = ? and app_sid = ?',
            [data.account, data.app_sid],
            function (err, result) {
                if (err) {
                    cb(err);
                    connection.release();
                    return;
                }
                var agentInfo = (result && result[0]) ? result[0] : undefined;
                if (!agentInfo) {
                    cb(null, null);
                    connection.release();
                    return;
                }

                var uids = [];
                result.forEach(function (o) {
                    uids.push(o.uid);
                });
                console.log(uids);
                connection.query('select * from order_list where uid in (?) and app_sid = ? and status = 13',
                    [uids, data.app_sid],
                    function (err, result) {
                        if (err) {
                            cb(err);
                            connection.release();
                            return;
                        }
                        cb(null, result);
                        connection.release();
                    });

            });
    });
}

//获取下级及下下级订单，算佣金
userAppDao.getCommission = function (data, cb) {

    if (!data) return cb(new Error(500));
    DBFactory.getConnection(function (error, connection) {
        if (error) {
            cb(error);
            return;
        }

        data.isOne = true; //查下级
        getCommssionList(data, connection, function (err, result) {
            if (err) {
                cb(err);
                connection.release(); //错误释放
                return;
            }
            var lowAgentList = {};
            lowAgentList.lowAgentList = result;

            data.isOne = false //查下下级
            getCommssionList(data, connection, function (err, result) {
                if (err) {
                    cb(err);
                    connection.release(); //错误释放
                    return;
                }

                lowAgentList.lowAgentList2 = result;
                cb(null, lowAgentList);
                connection.release();   //完成释放
            });
        });
    });
}


function getCommssionList(data, connection, cb) {

    //是否是直接推荐人 是语句1 不是 语句2
    var sql = data.isOne == true ?
        "SELECT a.vip_name,a.created_time,a.vip_lvl,a.pay_amount,a.commission1 AS commission,b.recommender,c.name,c.account FROM order_list a " +
        "LEFT JOIN user_app_new_agent b ON a.uid = b.uid AND a.app_sid = b.app_sid " +
        "LEFT JOIN users c ON a.uid = c.uid WHERE a.status = 13 AND a.commission1 > 0 AND recommender = ? " :
        "SELECT a.vip_name,a.created_time,a.vip_lvl,a.pay_amount,a.commission2 AS commission,b.recommender,c.name,c.account FROM order_list a " +
        "LEFT JOIN user_app_new_agent b ON a.uid = b.uid AND a.app_sid = b.app_sid " +
        "LEFT JOIN users c ON a.uid = c.uid WHERE a.status = 13 AND a.commission2 > 0 AND recommender2 = ? ";
    var value = [data.account];

    if (data.startTime && data.endTime) {
        data.startTime += ' 00:00:00';
        data.endTime += ' 23:59:59';
        sql += 'AND a.created_time >= ? AND a.created_time <= ? ';
        value.push(data.startTime, data.endTime);
    } else if (data.startTime) {
        var startTime = data.startTime + ' 00:00:00';
        var endTime = data.startTime + ' 23:59:59';
        sql += 'AND a.created_time >= ? AND a.created_time <= ? ';
        value.push(startTime, endTime);
    }

    var pageNum = data.pageNum ? data.pageNum : 10;
    var page = data.page ? data.page : 1;
    sql += 'LIMIT ' + (page - 1) * pageNum + ',' + pageNum;

    connection.query(sql, value, function (err, result) {
        if (err) {
            cb(err);
            return;
        }
        cb(null, result);
    });

}

//获得所有产品的注册信息:用户注册量，产品sid，产品名称
userAppDao.getAppRisInfos = function (data, cb) {
    if (!data) {
        cb(new Error(500));
    } else {
        var sql = 'select a.app_sid,b.name from user_app_new_agent a left join apps b on a.app_sid = b.sid';
        var sqlValue = [];
        // if (data.date) {
        // if (data.date == '2day') {//近48小时的数据
        //     // sql += ' where DATE_SUB(CURDATE(), INTERVAL 1 DAY) <= date(a.created_time)';
        //     sql += ' where to_days(a.created_time) = to_days(now())-1';
        // } else if (data.date == '30day') {//近30天的数据
        //     sql += ' where DATE_SUB(CURDATE(), INTERVAL 29 DAY) <= date(a.created_time)';
        // } else if (data.date == '6month') {//近6个月的数据
        //     sql += ' where DATE_SUB(CURDATE(), INTERVAL 6 MONTH) <= date(a.created_time)';
        // }
        if (data.app_sid) {
            sql += ' where a.app_sid = ?';
            sqlValue.push(data.app_sid);
        } else {
            sql += ' group by a.app_sid';
        }
        DBFactory.poolQuery(sql, sqlValue,
            function () {
                cb("no connection");
            }, function (err, result) {
                if (err) {
                    cb(err);
                    return;
                }
                cb(null, result);
            });
    }
}

//获得产品的用户注册信息
userAppDao.getAppData = function (data, cb) {
    if (!data) {
        cb(new Error(500));
    } else {
        var sql = {};
        var sqlValue = [];
        if (data.date == '2day') {//近48小时的数据
            sql = 'SELECT app_sid,COUNT(uid) as userCount, FLOOR(TIMESTAMPDIFF(SECOND, CAST((CAST(SYSDATE()AS DATE) + INTERVAL 1 DAY)AS DATETIME),created_time)/(3600)+48) as XiaBiao, DATE_FORMAT(created_time,"%Y-%m-%d %H点")'
                + ' as timeDisplay FROM user_app_new_agent where created_time >= DATE_SUB(CAST((CAST(SYSDATE()AS DATE) + INTERVAL 1 DAY)AS DATETIME), interval 2 day) ';
        } else if (data.date == '30day') {//近30天的数据
            sql = 'SELECT app_sid,COUNT(uid) as userCount, FLOOR(TIMESTAMPDIFF(SECOND, CURRENT_DATE, created_time) / (3600 * 24) +30 ) as XiaBiao, DATE_FORMAT(created_time,"%Y-%m-%d") as timeDisplay'
                + ' FROM user_app_new_agent'
                + ' where created_time >= DATE_SUB(CURRENT_DATE, interval 30 day)';
        } else if (data.date == '6month') {//近6个月的数据
            sql = 'select app_sid,COUNT(uid) as userCount, FORMAT(TIMESTAMPDIFF(SECOND, CURRENT_DATE, created_time) / (3600 * 24*30) + 6,1) as XiaBiao, ' +
                'DATE_FORMAT(created_time,"%Y-%m-%d") as timeDisplay ' +
                ' FROM user_app_new_agent where created_time >= DATE_SUB(CURRENT_DATE, interval 6 MONTH) ';
        }
        if (data.app_sid) {
            sql += 'and app_sid = ?';
            sqlValue.push(data.app_sid);
        }
        sql += ' group by timeDisplay order by timeDisplay ASC';
        DBFactory.poolQuery(sql, sqlValue,
            function () {
                cb("no connection");
            }, function (err, result) {
                if (err) {
                    cb(err);
                    return;
                }
                cb(null, result);
            });
    }
}

//获取每块用户注册量
userAppDao.getAppDateCount = function (data, cb) {
    if (!data) {
        cb(new Error(500));
    } else {
        DBFactory.getConnection(function (error, connection) {
            if (error) {
                cb(error);
            } else {
                var userInfo = {};
                async.waterfall([
                    function (callback) {
                        connection.beginTransaction(function (err) {
                            callback(err);
                        });
                    },

                    //获取所有用户量数据
                    function (callback) {
                        var sql = 'select COUNT(uid) count,app_sid from user_app_new_agent ';
                        var sqlValue = [];
                        if (data.app_sid) {
                            sql += ' where app_sid = ?';
                            sqlValue.push(data.app_sid);
                        } else {
                            sql += ' group by app_sid';
                        }
                        connection.query(sql, sqlValue, function (err, result) {
                            userInfo.sum = (result && result.length > 0) ? result : undefined;
                            callback(err, userInfo);
                        });
                    },

                    //获取昨日数据
                    function (userInfo, callback) {
                        var sql = 'select COUNT(uid) count,app_sid from user_app_new_agent where TO_DAYS(NOW()) - TO_DAYS(created_time) <= 1';
                        var sqlValue = [];
                        if (data.app_sid) {
                            sql += ' and app_sid = ?';
                            sqlValue.push(data.app_sid);
                        } else {
                            sql += ' group by app_sid';
                        }
                        connection.query(sql, sqlValue, function (err, result) {
                            userInfo.yesterday = (result && result.length > 0) ? result : undefined;
                            callback(err, userInfo);
                        });
                    },

                    //获取今日数据
                    function (userInfo, callback) {
                        var sql = 'select COUNT(uid) count,app_sid from user_app_new_agent  where to_days(created_time) = to_days(now())';
                        var sqlValue = [];
                        if (data.app_sid) {
                            sql += ' and app_sid = ?';
                            sqlValue.push(data.app_sid);
                        } else {
                            sql += ' group by app_sid';
                        }
                        connection.query(sql, sqlValue, function (err, result) {
                            userInfo.today = (result && result.length > 0) ? result : undefined;
                            callback(err, userInfo);
                        });
                    },

                    //获取本月数据
                    function (userInfo, callback) {
                        var sql = "select COUNT(uid) count,app_sid from user_app_new_agent   where DATE_FORMAT(created_time, '%Y%m' ) = DATE_FORMAT(CURDATE( ) , '%Y%m')";
                        var sqlValue = [];
                        if (data.app_sid) {
                            sql += ' and app_sid = ?';
                            sqlValue.push(data.app_sid);
                        } else {
                            sql += ' group by app_sid';
                        }
                        connection.query(sql, sqlValue, function (err, result) {
                            userInfo.thisMonth = (result && result.length > 0) ? result : undefined;
                            callback(err, userInfo);
                        });
                    },

                    //获取本周数据
                    function (userInfo, callback) {
                        var sql = "select COUNT(uid) count,app_sid from user_app_new_agent   where YEARWEEK(date_format(created_time,'%Y-%m-%d')) = YEARWEEK(now())";
                        var sqlValue = [];
                        if (data.app_sid) {
                            sql += ' and app_sid = ?';
                            sqlValue.push(data.app_sid);
                        } else {
                            sql += ' group by app_sid';
                        }
                        connection.query(sql, sqlValue, function (err, result) {
                            userInfo.thisWeek = (result && result.length > 0) ? result : undefined;
                            callback(err, true, userInfo);
                        });
                    },
                ], function (DbErr, isSuccess, userInfo) {
                    if (DbErr) {
                        connection.rollback(function () {
                            connection.release();
                            cb(DbErr);
                        });
                    } else if (!isSuccess) {
                        connection.rollback(function () {
                            connection.release();
                            cb(userInfo);
                        });
                    } else {
                        connection.commit(function (e) {
                            if (e) {
                                connection.rollback(function () {
                                    connection.release();
                                    cb(e);
                                });
                            } else {
                                connection.release();
                                cb(null, userInfo);
                            }
                        });
                    }
                });
            }
        });

    }
}

//获取用户具体vip权限
userAppDao.getVipSid = function (data, cb) {
    if (!data) {
        cb(new Error(500));
    } else {
        DBFactory.poolQuery('select * from user_vip where uid = ? and app_sid = ? and vip_sid = ? limit 1', [data.uid, data.app_sid, data.vip_sid],
            function () {
                cb('no connection');
            }, function (err, result) {
                if (err) {
                    cb(err);
                    console.log(err);
                    return;
                }
                if (result.length > 0) {
                    cb(null, result[0]);
                } else {
                    cb('该用户不存在该权限');
                }
            });
    }
}

//查看用户是否已经注册
userAppDao.getUserApp = function (data, cb) {
    if (!data) {
        cb(new Error(500));
    } else {
        DBFactory.poolQuery('select * from users where account = ?', [data],
            function () {
                cb("no connection");
            }, function (err, result) {
                if (err) {
                    cb(err);
                    return;
                }
                cb(null, result);
            });
    }
}

